import io
import os
import datetime
import time

import pandas as pd


def Create_sql(file_name):
    # 项目路径
    BASE_DIR = os.path.dirname(os.path.abspath('base_dir'))
    # 静态文件夹的路径,项目路径拼接向下的文件夹
    STATIC_DIR = os.path.join(BASE_DIR, 'static')
    excels_path = os.path.join(STATIC_DIR, 'excels')
    sqls_path = os.path.join(STATIC_DIR, 'sqls')

    # 配置字符串模板
    colum_name = """%s %s COMMENT '%s',%s %s COMMENT '%s',"""
    colum_name_tm = """%s %s COMMENT '%s',%s"""
    colum_name2 = """%s %s COMMENT '%s',%s %s COMMENT '%s'"""

    create_sql = """CREATE TABLE IF NOT EXISTS %s (%s) COMMENT '%s';"""
    create_sql_fen = """CREATE TABLE IF NOT EXISTS %s (%s) COMMENT '%s' partitioned BY (%s);"""

    colum_name_fen = """%s %s COMMENT '%s'"""
    colum_name_fen_dou = """%s %s COMMENT '%s' ,"""

    # 定义嵌套字符串模板行
    lines = ''
    partitioned_lines = ''

    # 读取文件
    file_loc = excels_path + "\\" + file_name
    df = pd.read_excel(file_loc, index_col=None, header=0)

    # 时间戳
    times = str(int(time.mktime(time.localtime())))

    # 创建表格文件
    ct = io.open(sqls_path + "\\create_sql_" + times + ".sql", 'a',
                 encoding='utf-8')

    ct_txt = io.open(sqls_path + "\\create_sql.txt", 'a', encoding='utf-8')

    for i in range(0, len(df)):
        print("创建表第" + str(i) + "行")
        if i < len(df) - 1:
            if df[i:i + 1]["物理表名称"].values[0] == df[i + 1:i + 2]["物理表名称"].values[0]:
                line = ''
                line = colum_name % (
                    df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i], df["脱敏后字段名称"][i], df["脱敏后数据类型"][i], df["脱敏后中文名称"][i])
                lines += line

                # 判断分区字段,区分处理
                if (df[i:i + 1]["是否分区字段"].values[0] == "是"):
                    if (df[i:i + 1]["是否多分区"].values[0] == "是"):
                        partitioned_line = ''
                        partitioned_line = colum_name_fen_dou % (df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i])
                        partitioned_lines += partitioned_line
                    else:
                        partitioned_line = ''
                        partitioned_line = colum_name_fen % (df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i])
                        partitioned_lines += partitioned_line

            else:
                line = ''
                line = colum_name2 % (
                    df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i], df["脱敏后字段名称"][i], df["脱敏后数据类型"][i], df["脱敏后中文名称"][i])
                lines += line
                if (df[i:i + 1]["是否分区字段"].values[0] == "是"):
                    partitioned_line = ''
                    partitioned_line = colum_name_fen % (df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i])
                    partitioned_lines += partitioned_line
                if len(partitioned_lines) < 1:
                    final_create_sql = ''
                    final_create_sql = create_sql % (df["物理表名称"][i], lines, df["物理表中文名称"][i])
                else:
                    final_create_sql = ''
                    final_create_sql = create_sql_fen % (df["物理表名称"][i], lines, df["物理表中文名称"][i], partitioned_lines)
                    lines = ''
                    partitioned_lines = ''
                    ct.write(final_create_sql + "\n")
                    ct_txt.write(final_create_sql + "\n")
        else:
            line = ''
            line = colum_name2 % (
                df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i], df["脱敏后字段名称"][i], df["脱敏后数据类型"][i], df["脱敏后中文名称"][i])
            lines += line
            if (df[i:i + 1]["是否分区字段"].values[0] == "是"):
                partitioned_line = ''
                partitioned_line = colum_name_fen % (df["字段名称"][i], df["数据类型"][i], df["字段中文名称"][i])
                partitioned_lines += partitioned_line
            if len(partitioned_lines) < 1:
                final_create_sql = ''
                final_create_sql = create_sql % (df["物理表名称"][i], lines, df["物理表中文名称"][i])
            else:
                final_create_sql = ''
                final_create_sql = create_sql_fen % (df["物理表名称"][i], lines, df["物理表中文名称"][i], partitioned_lines)
                lines = ''
                partitioned_lines = ''
                ct.write(final_create_sql + "\n")
                ct_txt.write(final_create_sql + "\n")
    ct.close()
    ct_txt.close()
    return "create_sql_" + times + ".sql"
